#*********************************************************************************
# ** 文件名称：hive_fdm_order_info.sh
# ** 功能描述：把数据从bdm层抽取到fdm层fdm_order_info表
# ** 创建者：xiaoxu
# ** 修改日期：19-06-13
# ** 修改人：
# ** 修改内容:
#*********************************************************************************
#!/usr/bin/env bash

DAT=`date -d '-1 day' "+%Y-%m-%d"`
sysdate=` date  "+%Y-%m-%d" `

#如果有参数传入，重跑脚本时使用（06-13号跑06-09的任务时）
#$1代表执行脚本时，传入的第一个参数 $n,第n个参数
if [ $1 ]; then
  DAT=$1
fi

echo "${DAT}"

#from_unixtime(unix_timestamp('2017-04-13 21:44:29.0')),

SQL="
insert overwrite table fdm.fdm_order_info partition(day='"${DAT}"')
    select
        o.id int,
        o.order_no,
        o.user_id,
        o.shipping_id,
        o.payment,
        o.payment_type,
        o.postage,
        o.status,

        from_unixtime(unix_timestamp(o.payment_time)) payment_time,
        from_unixtime(unix_timestamp(o.send_time)) send_time,
        from_unixtime(unix_timestamp(o.end_time)) end_time,
        from_unixtime(unix_timestamp(o.close_time)) close_time,
        from_unixtime(unix_timestamp(o.create_time)) create_time,
        from_unixtime(unix_timestamp( o.update_time)) update_time,



        oi.product_id,
        oi.product_name,
        oi.product_image,
        oi.current_unit_price,
        oi.quantity,
        oi.total_price,

        s.receiver_name,
        s.receiver_phone,
        s.receiver_mobile,
        s.receiver_province,
        s.receiver_city,
        s.receiver_district,
        s.receiver_address,
        s.receiver_zip

    from bdm.mmall_order as o
        left join bdm.mmall_order_item as oi
    on (o.order_no = oi.order_no)
        left join bdm.mmall_shipping as s
    on (o.user_id = oi.user_id)
    where o.day='"${DAT}"';
    "

echo "${SQL}"

hive -e "${SQL}"